package util;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;
import model.Cover;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2015/6/13 0013.
 */
public class ExcelUtil {
    public static final String EXCEL_PATH = "C:\\Users\\Administrator\\Desktop\\A3\\01经济类.xls";

    public static void main(String[] args) {
//        try {
//            WritableWorkbook wwb = null;
//            //首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
//            wwb = Workbook.createWorkbook(new File(EXCEL_PATH));
//            if(wwb!=null){
//                //创建一个可写入的工作表
//                WritableSheet ws = wwb.createSheet("sheet1", 0);
//                for(int i=0;i<10;i++){
//                    for(int j=0;j<5;j++){
//                        Label labelC = new Label(j,i,"这是第"+(i+1)+"行，第"+(j+1)+"列");
//                        ws.addCell(labelC);
//                    }
//                }
//                wwb.write();
//                wwb.close();
//            }
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        System.out.println("ok");
//        File excel = new File(EXCEL_PATH);
//        if(excel.exists()){
//            List<Cover> coverList = readExcel(new File(EXCEL_PATH));
//            for (Cover cover : coverList) {
//                System.out.println(cover);
//            }
//        }else{
//            System.out.println("文件不存在");
//        }
        List<Cover> coverList = readExcel(new File(EXCEL_PATH));
        writeExcel(new File("d:\\test1.xls"),coverList);

    }
    public static List<Cover> readExcel(File excel){
        WorkbookSettings ws = new WorkbookSettings();
        ws.setEncoding("UTF-8");
        List<Cover> coverList = new ArrayList<Cover>();
        try {
            InputStream is = new FileInputStream(excel);
            Workbook wb = Workbook.getWorkbook(is); // 得到工作薄
            Sheet[] sheets = wb.getSheets(); // 获得所有的工作表
            for (Sheet sheet : sheets) {
                int rsRows = sheet.getRows(); // 得到excel的总行数
                int columncount = sheet.getColumns();// 获得excel的总列数
                System.out.println("共" + rsRows + "行 " + columncount + "行");
                for (int i = 0; i < rsRows; i++) {
                    Cell cell0 = sheet.getCell(0,i);
                    Cell cell1 = sheet.getCell(1,i);
                    Cover cover = new Cover();
                    cover.setFileName(cell0.getContents());
                    cover.setPath(cell1.getContents());
                    coverList.add(cover);
                    System.out.println("已读取（列）" + i);
                }
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return coverList;
    }

    public static void writeExcel(File Destination, List<Cover> coverList){
        WritableWorkbook wwb = null;
        try {
            //首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
            wwb = Workbook.createWorkbook(Destination);
            if(wwb!=null){
                //创建一个可写入的工作表
                WritableSheet ws = wwb.createSheet("sheet1", 0);
                System.out.println("共" + coverList.size() + "行");
                for(int i=0;i<coverList.size();i++){
                    Label label0 = new Label(0,i,coverList.get(i).getFileName());
                    ws.addCell(label0);
                    Label label1 = new Label(1,i,coverList.get(i).getPath());
                    ws.addCell(label1);
                    System.out.println("写完第" + i + "行");
                }
                wwb.write();
                wwb.close();
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        }finally {
            System.out.println("已完成写入");
        }
    }
}
